package tool

import (
	"errors"
	"github.com/xuri/excelize/v2"
	"strconv"
	"strings"
	"time"
)

// 读取excel为map数组
func ReadExcel(excel string, headIndex int) (map[string][]map[string]string, error) {
	f, err := excelize.OpenFile(excel)
	if err != nil {
		return nil, err
	}
	defer f.Close()

	sheetList := make(map[string][]map[string]string)
	for i := 0; i < f.SheetCount; i++ {
		sheetName := f.GetSheetName(i)
		rows, err := f.GetRows(sheetName)
		if err != nil {
			return nil, err
		}
		if len(rows) <= headIndex {
			return nil, errors.New("行数不够")
		}

		var sheet []map[string]string
		headers := rows[headIndex] // 提取表头

		for rowKey := headIndex + 1; rowKey < len(rows); rowKey++ {
			item := make(map[string]string) // 初始化 item 为一个空的 map
			for headKey, headValue := range headers {
				headValue = strings.Trim(headValue, " \n\r\t")
				if headKey < len(rows[rowKey]) {
					item[headValue] = strings.Trim(rows[rowKey][headKey], " \n\r\t")
				} else {
					item[headValue] = ""
				}
			}
			sheet = append(sheet, item)
		}
		sheetList[sheetName] = sheet
	}

	return sheetList, nil
}

// ExcelToDate 将Excel日期数字转换为年月日
func ExcelToDate(excelDate float64, format string) string {
	// Excel 的正确基准日期：1900-01-01（值为 1）
	baseDate := time.Date(1900, 1, 1, 0, 0, 0, 0, time.UTC)

	// 调整 Excel 的 1900 闰年错误（1900-02-29 不存在，但 Excel 认为它是 60）
	if excelDate >= 60 {
		excelDate -= 1
	}

	// 计算天数和小数部分（时间）
	days := int(excelDate)
	fraction := excelDate - float64(days)

	// 添加天数
	resultDate := baseDate.AddDate(0, 0, days-1) // -1 因为 Excel 的 1 = 1900-01-01

	// 添加时间部分（如果有）
	if fraction > 0 {
		nanoseconds := int64(fraction * 24 * 60 * 60 * 1e9)
		resultDate = resultDate.Add(time.Duration(nanoseconds))
	}

	return resultDate.Format(format)
}

// 获取列名
func GetCol(colNum int, row int) string {
	return GetHead(colNum) + strconv.Itoa(row)
}

// 表头标识符
func GetHead(colNum int) string {
	cols := []string{"A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z"}
	if colNum < 26 {
		return cols[colNum]
	} else {
		return cols[colNum/26] + cols[colNum%26]
	}
}

type ExcelSheet[T any] struct {
	Name    string
	Headers []ExcelSheetHeader
	Data    []ExcelCol[T]
}

type ExcelCol[T any] struct {
	Value T
	Style *excelize.Style
}

type ExcelSheetHeader struct {
	Label string
	Key   string
	Width float64 //宽度必须小于或等于255
	Style *excelize.Style
}

// 导出excel
func ExportExcel[T any](sheetNameList []ExcelSheet[T]) (*excelize.File, error) {
	f := excelize.NewFile()

	for sheetIndex, sheet := range sheetNameList {
		//创建sheet
		sheetName := sheetNameList[sheetIndex]
		if sheetIndex == 0 {
			err := f.SetSheetName("Sheet1", sheetName.Name)
			if err != nil {
				return nil, err
			}
		} else {
			_, err := f.NewSheet(sheetName.Name)
			if err != nil {
				return nil, err
			}
		}

		//设置表头
		c := 0
		//在 Go 中，map 是一个无序的数据结构，它不保证键值对的遍历顺序。因此，当遍历 map 时，键值对的顺序可能会发生变化,因此需要keyArr提取表头切片，切片顺序不会变
		var keyArr []string

		for _, head := range sheetName.Headers {
			col := GetCol(c, 1)
			if head.Width > 0 {
				headCol := GetHead(c)
				f.SetColWidth(sheetName.Name, headCol, headCol, head.Width)
			}

			// 设置列样式
			if head.Style != nil {
				styleID, err := f.NewStyle(head.Style)
				if err == nil {
					f.SetColStyle(sheetName.Name, col, styleID)
				}
			}
			f.SetCellValue(sheetName.Name, col, head.Label)

			keyArr = append(keyArr, head.Key)
			c++
		}

		//写入数据
		for rowIndex, row := range sheet.Data {
			c = 0
			for _, key := range keyArr {
				col := GetCol(c, rowIndex+2)
				// 设置单元格样式
				if row.Style != nil {
					styleID, err := f.NewStyle(row.Style)
					if err == nil {
						f.SetCellStyle(sheetName.Name, col, col, styleID)
					}
				}

				mapRow := StructToMap(row.Value)
				val, ok := mapRow[key]

				if ok {
					val = StructValue(DerefIfPtr(val))
					f.SetCellValue(sheetName.Name, col, val)
				} else {
					f.SetCellValue(sheetName.Name, col, "")
				}
				c++
			}
		}
	}

	//// 将excel写入到buffer中
	//	var buffer bytes.Buffer
	//	if err := excel.Write(&buffer); err != nil {
	//		c.String(http.StatusInternalServerError, "Failed to create Excel file")
	//		return
	//	}
	//
	//	// 设置响应头
	//	c.Header("Content-Type", "application/octet-stream")
	//	c.Header("Content-Disposition", "attachment; filename=\"example.xlsx\"")
	//
	//	// 将buffer写入到响应中
	//	c.Data(http.StatusOK, "application/octet-stream", buffer.Bytes())

	return f, nil
}
